package com.ambitor.grass.sql.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;

import com.ambitor.grass.sql.dao.BaseDao;
import com.ambitor.grass.sql.dao.SuperDao;
import com.ambitor.grass.sql.data.Parameter;
import com.ambitor.grass.sql.parser.BatchParsedResult;
import com.ambitor.grass.sql.parser.ParsedResult;
import com.ambitor.grass.sql.parser.SqlParser;
import com.ambitor.grass.sql.util.DaoHelper;
import com.ambitor.grass.util.data.DataMap;
import com.ambitor.grass.util.data.DatasetList;
import com.ambitor.grass.util.data.DatasetResult;
import com.ambitor.grass.util.data.IData;
import com.ambitor.grass.util.data.IDataset;

public class SuperDaoImpl implements SuperDao {

	private Connection conn;

	private BaseDao baseDao;

	public SuperDaoImpl(Connection conn) {
		this.conn = conn;
		baseDao = new BaseDaoImpl(conn);
	}

	public SuperDaoImpl(BaseDao baseDao) {
		this.baseDao = baseDao;
		this.conn = baseDao.getConnection();
	}

	public BaseDao getBaseDao() {
		return baseDao;
	}

	public void setBaseDao(BaseDao baseDao) {
		this.conn = baseDao.getConnection();
		this.baseDao = baseDao;
	}

	public Connection getConnection() {
		return conn;
	}

	public int delete(String tableName, IData data) throws Exception {
		return this.delete(tableName, data, null);
	}

	public int delete(String tableName, IData data, String[] keys)
			throws Exception {
		ParsedResult parseResult = SqlParser.parseDeleteSql(conn, tableName,
				data, keys);
		return baseDao.executeUpdate(parseResult.getParsedSql(), parseResult
				.getParameter());
	}

	public int[] delete(String tableName, IDataset dataset) throws Exception {
		BatchParsedResult parseResult = SqlParser.parseDeleteSql(conn,
				tableName, dataset, null);
		return baseDao.executeBatch(parseResult.getParsedSql(), parseResult
				.getParameters());
	}

	public int getCount(String str) throws Exception {
		ResultSet rs = baseDao.executeQuery(DaoHelper.getCountSql(str));
		int value = (rs.next()) ? rs.getInt(1) : 0;
		return value;
	}

	public int getCount(String str, Parameter data) throws Exception {
		ResultSet rs = baseDao.executeQuery(DaoHelper.getCountSql(str), data);
		int value = (rs.next()) ? rs.getInt(1) : 0;
		return value;
	}

	public int getCount(String str, IData data) throws Exception {
		ResultSet rs = baseDao.executeQuery(DaoHelper.getCountSql(str), data);
		int value = (rs.next()) ? rs.getInt(1) : 0;
		return value;
	}

	public int insert(String tableName, IData data) throws Exception {
		ParsedResult parseResult = SqlParser.parseInsertSql(conn, tableName,
				data);
		return baseDao.executeUpdate(parseResult.getParsedSql(), parseResult
				.getParameter());
	}

	public String insertAndReturnLastInsertId(String tableName, IData data)
			throws Exception {
		ParsedResult parseResult = SqlParser.parseInsertSql(conn, tableName,
				data);
		return baseDao.executeInsertAndReturnLastInsertId(parseResult
				.getParsedSql(), parseResult.getParameter());
	}

	public int[] insert(String tableName, IDataset dataset) throws Exception {
		BatchParsedResult parseResult = SqlParser.parseInsertSql(conn,
				tableName, dataset);
		return baseDao.executeBatch(parseResult.getParsedSql(), parseResult
				.getParameters());
	}

	public IData queryByPK(String tableName, IData param) throws Exception {
		ParsedResult parseResult = SqlParser.parseQuerySql(conn, tableName,
				null, param, null);
		IDataset dataset = queryListBySql(parseResult.getParsedSql(),
				parseResult.getParameter());
		return dataset.count() == 0 ? new DataMap() : dataset.getData(0);
	}

	public IData queryByPK(String tableName, String columns, IData param)
			throws Exception {
		ParsedResult parseResult = SqlParser.parseQuerySql(conn, tableName,
				columns, param, null);
		IDataset dataset = queryListBySql(parseResult.getParsedSql(),
				parseResult.getParameter());
		return dataset.count() == 0 ? new DataMap() : dataset.getData(0);
	}

	public IDataset queryList(String tableName) throws Exception {
		String sql = "select * from " + tableName;
		return queryListBySql(sql);
	}

	@Override
	public IDataset queryList(String tableName, int start, int size)
			throws Exception {
		String sql = "select * from " + tableName;
		return queryListBySql(sql, start, size);
	}

	public IDataset queryList(String tableName, String columns)
			throws Exception {
		String sql = "select " + columns + " from " + tableName;
		return queryListBySql(sql);
	}

	@Override
	public IDataset queryList(String tableName, String columns, int start,
			int size) throws Exception {
		String sql = "select " + columns + " from " + tableName;
		return queryListBySql(sql, start, size);
	}

	public IDataset queryList(String tableName, IData param) throws Exception {
		ParsedResult parseResult = SqlParser.parseQuerySql(conn, tableName,
				null, param, param.getNames());
		return queryListBySql(parseResult.getParsedSql(), parseResult
				.getParameter());
	}

	@Override
	public IDataset queryList(String tableName, IData param, int start, int size)
			throws Exception {
		ParsedResult parseResult = SqlParser.parseQuerySql(conn, tableName,
				null, param, param.getNames());
		IDataset dataset = queryListBySql(parseResult.getParsedSql(),
				parseResult.getParameter(), start, size);
		return dataset;
	}

	public IDataset queryList(String tableName, String columns, IData param)
			throws Exception {
		ParsedResult parseResult = SqlParser.parseQuerySql(conn, tableName,
				columns, param, param.getNames());
		return queryListBySql(parseResult.getParsedSql(), parseResult
				.getParameter());
	}

	@Override
	public IDataset queryList(String tableName, String columns, IData param,
			int start, int size) throws Exception {
		ParsedResult parseResult = SqlParser.parseQuerySql(conn, tableName,
				columns, param, param.getNames());
		IDataset dataset = queryListBySql(parseResult.getParsedSql(),
				parseResult.getParameter(), start, size);
		return dataset;
	}

	public IDataset queryListBySql(String sql) throws Exception {
		ResultSet rs = baseDao.executeQuery(sql);
		return new DatasetResult(rs);
	}

	@Override
	public IDataset queryListBySql(String sql, int start, int size)
			throws Exception {
		IDataset dataset = queryListBySql(sql, new Parameter(), start, size);
		return dataset;
	}

	public IDataset queryListBySql(String sql, Parameter param)
			throws Exception {
		if ((param == null) || (param.size() == 0))
			return queryListBySql(sql);

		ResultSet rs = baseDao.executeQuery(sql, param);
		return new DatasetResult(rs);
	}

	public IDataset queryListBySql(String sql, Parameter param, int start,
			int size) throws Exception {
		if (param == null)
			param = new Parameter();
		int count = this.getCount(sql, param);
		sql = DaoHelper.getPagingSql(sql, param, start, size);
		IDataset dataset = queryListBySql(sql, param);
		dataset.setCount(count);
		return dataset;
	}

	public IDataset queryListBySql(String sql, IData param) throws Exception {
		ResultSet rs = baseDao.executeQuery(sql, param);
		return new DatasetResult(rs);
	}

	public IDataset queryListBySql(String sql, IData param, int start, int size)
			throws Exception {
		if (param == null)
			param = new DataMap();
		int count = this.getCount(sql, param);
		sql = DaoHelper.getPagingSql(sql, param, start, size);
		IDataset dataset = queryListBySql(sql, param);
		dataset.setCount(count);
		return dataset;
	}

	@SuppressWarnings("unchecked")
	public int save(String tableName, IData data) throws Exception {
		IData old_data = queryByPK(tableName, data);
		if (old_data == null || old_data.size() == 0) {
			return this.insert(tableName, data);
		}
		old_data.putAll(data);
		return update(tableName, old_data);
	}

	public int update(String tableName, IData data) throws Exception {
		return this.update(tableName, data, null);

	}

	public int update(String tableName, IData data, String[] keys)
			throws Exception {
		ParsedResult parseResult = SqlParser.parseUpdateSql(conn, tableName,
				data, keys);
		return baseDao.executeUpdate(parseResult.getParsedSql(), parseResult
				.getParameter());
	}

	public int[] update(String tableName, IDataset dataset) throws Exception {
		if (dataset.count() == 0) {
			return null;
		}
		BatchParsedResult parseResult = SqlParser.parseUpdateSql(conn,
				tableName, dataset, null);
		return baseDao.executeBatch(parseResult.getParsedSql(), parseResult
				.getParameters());
	}

	public IDataset executeBaseOnSqlSql(String tableName, String sqlRef)
			throws Exception {
		return executeBaseOnSqlSql(tableName, sqlRef, new DataMap());
	}

	@Override
	@SuppressWarnings("unchecked")
	public IDataset executeBaseOnSqlSql(String tableName, String sqlRef,
			int start, int size) throws Exception {
		IData data = new DataMap();
		data.put("TABLE_NAME", tableName);
		data.put("SQL_REF", sqlRef);
		IData sqlData = queryByPK("SQL_SQL", "SQL_STMT", data);
		String sql = sqlData.getString("SQL_STMT");
		IDataset dataset = new DatasetList();
		if (sql != null && sql.trim().toUpperCase().startsWith("SELECT")) {
			dataset = queryListBySql(sql, start, size);
		}
		return dataset;
	}

	@SuppressWarnings("unchecked")
	public IDataset executeBaseOnSqlSql(String tableName, String sqlRef,
			IData param) throws Exception {
		IData data = new DataMap();
		data.put("TABLE_NAME", tableName);
		data.put("SQL_REF", sqlRef);
		IData sqlData = queryByPK("SQL_SQL", "SQL_STMT", data);
		String sql = sqlData.getString("SQL_STMT");
		if (sql == null)
			return new DatasetList();
		if (sql.trim().toUpperCase().startsWith("SELECT")) {
			return queryListBySql(sql, param);
		} else {
			int result = baseDao.executeUpdate(sql, param);
			IData reslut_data = new DataMap();
			reslut_data.put("RESULT", result);
			IDataset result_dataset = new DatasetList();
			result_dataset.add(reslut_data);
			return result_dataset;
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public IDataset executeBaseOnSqlSql(String tableName, String sqlRef,
			IData param, int start, int size) throws Exception {
		IData data = new DataMap();
		data.put("TABLE_NAME", tableName);
		data.put("SQL_REF", sqlRef);
		IData sqlData = queryByPK("SQL_SQL", "SQL_STMT", data);
		String sql = sqlData.getString("SQL_STMT");
		IDataset dataset = new DatasetList();
		if (sql != null && sql.trim().toUpperCase().startsWith("SELECT")) {
			dataset = queryListBySql(sql, param, start, size);
		}
		return dataset;
	}

	@SuppressWarnings("unchecked")
	public IDataset executeBaseOnSqlSql(String tableName, String sqlRef,
			Parameter param) throws Exception {
		IData data = new DataMap();
		data.put("TABLE_NAME", tableName);
		data.put("SQL_REF", sqlRef);
		IData sqlData = queryByPK("SQL_SQL", "SQL_STMT", data);
		String sql = sqlData.getString("SQL_STMT");
		if (sql == null)
			return new DatasetList();
		if (sql.trim().toUpperCase().startsWith("SELECT")) {
			return queryListBySql(sql, param);
		} else {
			int result = baseDao.executeUpdate(sql, param);
			IData reslut_data = new DataMap();
			reslut_data.put("RESULT", result);
			IDataset result_dataset = new DatasetList();
			result_dataset.add(reslut_data);
			return result_dataset;
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public IDataset executeBaseOnSqlSql(String tableName, String sqlRef,
			Parameter param, int start, int size) throws Exception {
		IData data = new DataMap();
		data.put("TABLE_NAME", tableName);
		data.put("SQL_REF", sqlRef);
		IData sqlData = queryByPK("SQL_SQL", "SQL_STMT", data);
		String sql = sqlData.getString("SQL_STMT");
		IDataset dataset = new DatasetList();
		if (sql != null && sql.trim().toUpperCase().startsWith("SELECT")) {
			dataset = queryListBySql(sql, param, start, size);
		}
		return dataset;
	}
}
